package com.x.erp.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.x.erp.model.Market;
import com.x.framework.dao.BaseDao;

@Repository
public class MarketDao extends BaseDao {

	private final static String selectMarketListSql = "SELECT "
			+ "T1.*, "
			+ "bc.CITY_NAME, "
			+ "bp.PROVINCE_NAME "
			+ "FROM "
			+ "ERP_MARKET T1 "
			+ "JOIN ERP_MARKET T2 ON T1.MARKET_CODE LIKE CONCAT(T2.MARKET_CODE, '%')  "
			+ "LEFT JOIN base_city bc ON bc.city_id = T1.city_id "
			+ "LEFT JOIN base_province bp ON bp.province_id = T1.province_id "
			+ "WHERE T2.MARKET_ID = ?";
	public List<Market> selectMarketList(Market market) throws Exception{
		StringBuilder sb = new StringBuilder(selectMarketListSql);
		List<Object> params = new ArrayList<Object>();
		params.add(market.getMarketId());
		if(this.isString(market.getMarketParentId())){
			sb.append("AND T1.MARKET_PARENT_ID = ? ");
			params.add(market.getMarketParentId());
		}
		if(this.isString(market.getMarketCode())){
			sb.append("AND T1.MARKET_CODE LIKE ? ");
			params.add(market.getMarketCode() + "%");
		}
		if(this.isString(market.getMarketName())){
			sb.append("AND T1.MARKET_NAME = ? ");
			params.add(market.getMarketName());
		}
		if(this.isString(market.getMarketMobile())){
			sb.append("AND T1.MARKET_MOBILE = ? ");
			params.add(market.getMarketMobile());
		}
		if(market.getSearchStartTime() != null){
			sb.append("AND T1.MARKET_CREATE_TIME >= ? ");
			params.add(market.getSearchStartTime());
		}
		if(market.getSearchEndTime() != null ){
			sb.append("AND T1.MARKET_CREATE_TIME <= ? ");
			params.add(market.getSearchEndTime());
		}
		return this.queryForList(sb.toString(), Market.class, params, market);
	}


	private final static String selectMarketNumSql = "SELECT "
			+ "COUNT(T1.MARKET_ID)"
			+ "FROM "
			+ "ERP_MARKET T1, "
			+ "ERP_MARKET T2 "
			+ "WHERE "
			+ "T2.MARKET_ID = ? AND T1.MARKET_CODE LIKE CONCAT(T2.MARKET_CODE, '%') ";
	public int selectMarketNum(Market market) throws Exception{
		StringBuilder sb = new StringBuilder(selectMarketNumSql);
		List<Object> params = new ArrayList<Object>();
		params.add(market.getMarketId());
		if(this.isString(market.getMarketParentId())){
			sb.append("AND T1.MARKET_PARENT_ID = ? ");
			params.add(market.getMarketParentId());
		}
		if(this.isString(market.getMarketCode())){
			sb.append("AND T1.MARKET_CODE LIKE ? ");
			params.add(market.getMarketCode() + "%");
		}
		if(this.isString(market.getMarketName())){
			sb.append("AND T1.MARKET_NAME = ? ");
			params.add(market.getMarketName());
		}
		if(this.isString(market.getMarketMobile())){
			sb.append("AND T1.MARKET_MOBILE = ? ");
			params.add(market.getMarketMobile());
		}
		if(market.getSearchStartTime() != null){
			sb.append("AND T1.MARKET_CREATE_TIME >= ? ");
			params.add(market.getSearchStartTime());
		}
		if(market.getSearchEndTime() != null ){
			sb.append("AND T1.MARKET_CREATE_TIME <= ? ");
			params.add(market.getSearchEndTime());
		}
		return this.queryForInt(sb.toString(), params);
	}






	private final static String selectMaxCodeSql = "SELECT MAX(MARKET_CODE) AS MAX_CODE FROM ERP_MARKET WHERE MARKET_PARENT_ID = ? ";
	public String selectMaxCode(String parentId) throws Exception {
		Object[] params = {parentId};
		RowMapper<String> mapper = new RowMapper<String>() {
			@Override
			public String mapRow(ResultSet rs, int rowNum) throws SQLException {
				return rs.getString("MAX_CODE");
			}
		};
		Object maxCode = this.queryForObject(selectMaxCodeSql, mapper, params);
		if (maxCode != null) {
			return maxCode.toString();
		} else  {
			return null;
		}
	}

}
